﻿CREATE PROCEDURE [dbo].[Text_BookTree_AddHead]
	@current NVARCHAR(450),
	@after NVARCHAR(450),
	@hashTag NVARCHAR(128),
	@text NVARCHAR(MAX),
	@currentId BIGINT = NULL,
	@bookTreeId BIGINT = NULL
AS
BEGIN
	SET @hashTag = RTRIM(LTRIM(LOWER(@hashTag)))
	SET @current = RTRIM(LTRIM(LOWER(@current)))
	SET @after = RTRIM(LTRIM(LOWER(@after)))

	DECLARE @bookId UNIQUEIDENTIFIER = NULL
	DECLARE @currentLevel TINYINT
	DECLARE @parentLevel TINYINT
	DECLARE @parentBookTreeId BIGINT

	IF @bookTreeId IS NOT NULL
	BEGIN
		SELECT @bookId = t.[BookId], @parentLevel = t.[Level], @parentBookTreeId = t.[BookTreeId]
		FROM [dbo].[text_BookTree_Cache] AS c
		INNER JOIN [dbo].[text_BookTree] AS t ON c.[BookTreeId] = t.[BookTreeId]
		WHERE c.[Url] = @current

		IF @bookId IS NULL
		BEGIN
			RAISERROR ('BookNotExist',9,1)
			RETURN
		END

		SET @currentLevel = @parentLevel + 1

		IF EXISTS (SELECT * FROM [dbo].[text_BookTree] WHERE [BookId] = @bookId AND [ParentBookTreeId] = @parentBookTreeId AND [HashTag] = @hashTag AND [BookTreeId] <> @bookTreeId)
		BEGIN
			RAISERROR ('HashTagExist',9,1)
			RETURN
		END

		UPDATE [Table]
		SET [HashTag] = @hashTag
			,[Text] = @text
			,[UpdateDate] = GETUTCDATE()
		FROM [dbo].[text_BookTree] AS [Table]
		JOIN [dbo].[text_BookTree_Cache] AS c ON c.[BookTreeId] = [Table].[ParentBookTreeId]
		WHERE [Table].[BookTreeId] = @bookTreeId AND [Table].[IsHead] = 1 AND c.[Url] = @current

		EXEC	[dbo].[Text_BookTree_Cache_Refresh]

	END
	ELSE IF @after = ''
	BEGIN
		DECLARE @result INT

		SELECT @parentBookTreeId = t.[BookTreeId], @bookId = t.[BookId], @parentLevel = t.[Level]
		FROM [dbo].[text_BookTree_Cache] AS c
		INNER JOIN [dbo].[text_BookTree] AS t ON c.[BookTreeId] = t.[BookTreeId]
		WHERE c.[Url] = @current

		SET @currentLevel = @parentLevel + 1

		IF @bookId IS NULL
		BEGIN
			RAISERROR ('BookNotExist',9,1)
			RETURN
		END

		IF EXISTS (SELECT * FROM [dbo].[text_BookTree] WHERE [BookId] = @bookId AND [ParentBookTreeId] = @parentBookTreeId  AND [HashTag] = @hashTag )
		BEGIN
			RAISERROR ('HashTagExist',9,1)
			RETURN
		END

		DECLARE @lastLevelBookTreeId BIGINT

		SELECT TOP 1 @lastLevelBookTreeId = [BookTreeId] 
		FROM [dbo].[text_BookTree] 
		WHERE [BookId] = @bookId AND [Level] = @currentLevel AND [IsHead] = 0 AND [ParentBookTreeId] = @parentBookTreeId
		ORDER BY [RightKey] DESC

		IF @lastLevelBookTreeId IS NULL
		BEGIN
			EXEC @result = [dbo].[Text_BookTree_Add] 
				@addParentTreeId = @parentBookTreeId
				,	@hashTag = @hashTag
				, @isHead = 1
				, @published = 0
				, @text = @text
				, @currentId = @currentId OUT
		END
		ELSE
		BEGIN
			EXEC @result = [dbo].[Text_BookTree_AddAfter]
				@afterBookTreeId = @lastLevelBookTreeId
				,	@hashTag = @hashTag
				, @isHead = 1
				, @published = 0
				, @text = @text
				, @currentId = @currentId OUT
		END

		IF @result = 0
		BEGIN
			EXEC	[dbo].[Text_BookTree_Cache_Refresh]
		END
	END
	ELSE
	BEGIN
		DECLARE @afterBookTreeId BIGINT

		SELECT @afterBookTreeId = t.[BookTreeId], @bookId = t.[BookId], @currentLevel = t.[Level]
		FROM [dbo].[text_BookTree_Cache] AS c
		INNER JOIN [dbo].[text_BookTree] AS t ON c.[BookTreeId] = t.[BookTreeId]
		WHERE c.[Url] = @after

		IF @bookId IS NULL
		BEGIN
			RAISERROR ('BookNotExist',9,1)
			RETURN
		END

		IF EXISTS (SELECT * FROM [dbo].[text_BookTree] WHERE [BookId] = @bookId AND [ParentBookTreeId] = @parentBookTreeId  AND [HashTag] = @hashTag)
		BEGIN
			RAISERROR ('HashTagExist',9,1)
			RETURN
		END


		EXEC @result = [dbo].[Text_BookTree_AddAfter] 
			@afterBookTreeId = @afterBookTreeId
			,	@hashTag = @hashTag
			, @isHead = 1
			, @published = 0
			, @text = @text
			, @currentId = @currentId OUT

		IF @result = 0
		BEGIN
			EXEC	[dbo].[Text_BookTree_Cache_Refresh]
		END

	END

	SELECT @currentId AS 'BookTreeId'
END